
        -- Author:		<ngvanty03@gmail.com>
        -- Create date: <Create Date,,>
        -- Description:	<Description,,>
        -- =============================================
        CREATE PROCEDURE  spGetAllLanguage	       
        AS
        BEGIN
	       Select [languageId],[name],[published],[order],[createdBy],[createdDate],[lastUpdatedBy],[lastUpdatedDate] from Language
        END
        GO
           
        -- Author:		<ngvanty03@gmail.com>
        -- Create date: <Create Date,,>
        -- Description:	<Description,,>
        -- =============================================
        CREATE PROCEDURE spGetLanguage  
        (
        @LanguageId varchar (6)
        )      
        AS
        BEGIN	       
	        SELECT [languageId],[name],[published],[order],[createdBy],[createdDate],[lastUpdatedBy],[lastUpdatedDate] from Language where [languageId]=@LanguageId
        END
        GO
        
            -- Author:ngvanty03@gmail.com		
            -- Create date: <Create Date,,>
            -- Description:	<Description,,>
            -- =============================================
            CREATE PROCEDURE spInsertLanguage
            (
            
@LanguageId varchar(6),
@Name nvarchar(50),
@Published varchar(2),
@Order int,
@CreatedBy nvarchar(256),
@CreatedDate datetime,
@LastUpdatedBy nvarchar(256),
@LastUpdatedDate datetime
            )
            AS
            begin
                Insert into Language([languageId],[name],[published],[order],[createdBy],[createdDate],[lastUpdatedBy],[lastUpdatedDate]) values (@LanguageId,@Name,@Published,@Order,@CreatedBy,@CreatedDate,@LastUpdatedBy,@LastUpdatedDate)
                if(@@ERROR<>0)--Insert Error
                    return 0;
                return 1;
            end
            GO
            -- Author:ngvanty03@gmail.com		
            -- Create date: <Create Date,,>
            -- Description:	<Description,,>
            -- =============================================
            CREATE PROCEDURE spUpdateLanguage
            (
            
@LanguageId varchar(6),
@Name nvarchar(50),
@Published varchar(2),
@Order int,
@CreatedBy nvarchar(256),
@CreatedDate datetime,
@LastUpdatedBy nvarchar(256),
@LastUpdatedDate datetime
            )
            AS
            begin
                Update Language set [name]=@Name,[published]=@Published,[order]=@Order,[createdBy]=@CreatedBy,[createdDate]=@CreatedDate,[lastUpdatedBy]=@LastUpdatedBy,[lastUpdatedDate]=@LastUpdatedDate where [languageId]=@LanguageId
                if(@@ERROR<>0)--Update Error
                    return 0;
                return 1;
            end
            GO
        -- Author:		<ngvanty03@gmail.com>
        -- Create date: <Create Date,,>
        -- Description:	<Description,,>
        -- =============================================
        
        CREATE PROCEDURE spDeleteLanguage  
        (
        @LanguageId varchar (6)
        )      
        AS
        BEGIN 
	        DELETE from Language  where [languageId]=@LanguageId
            if(@@ERROR<>0)--error
                return 0;
            return 1;
        END           
        GO
        --exec spSearchLanguageByCondition ' where 1=1 ',null,null,null,null

/*Author:Nguyen Van Ty
 Create date:12/06/2011
 Description:search Language by some Condition*/
-- =============================================
create proc  spSearchLanguage
(
   --Search condtiton
@Condition nvarchar(max)=null,
--sort infor
@SortField nvarchar(50)=null,
@SortExpression nvarchar(4)=null,
--Paging infor	
@PageIndex int=1,
@PageSize int=10

)
AS
BEGIN
  Declare @MaxValue int
  set @MaxValue=2147483647
  Declare @startRow int
  Declare @endRow int
  if(@SortField is null or @SortField='')
     set @SortField='languageId'
  if(@SortExpression is null or @SortExpression='')
     set @SortExpression='DESC'
  if(@PageSize is null or @PageSize<=0)
     set @PageSize=10
  if(@PageIndex is null or @PageIndex<1)
     set @PageIndex=1
  if(@PageSize<>@MaxValue)
     begin
       set @startRow = (@PageIndex-1) * @PageSize + 1;
       set @endRow =  @startRow+ @PageSize-1;
     end
  else
     begin
       set @startRow = 1;
       set @endRow =  @PageSize;
    end
  Declare @query nvarchar(max)
  Declare @totalSql nvarchar(max)
  set @totalSql='
  with result
    (Row,[languageId],[name],[published],[order],[createdBy],[createdDate],[lastUpdatedBy],[lastUpdatedDate])
  as(SELECT ROW_NUMBER() OVER (ORDER BY ' + @SortField +  + ' ' + @SortExpression + '  ) as Row,[languageId],[name],[published],[order],[createdBy],[createdDate],[lastUpdatedBy],[lastUpdatedDate]
     FROM Language '
  set @totalSql=@totalSql + @Condition + ')'
  set @totalSql=@totalSql + ' SELECT [languageId],[name],[published],[order],[createdBy],[createdDate],[lastUpdatedBy],[lastUpdatedDate],(Select count(*) from result) as TotalRow
  FROM result WHERE Row between ' + convert(varchar(12), @startRow) + ' AND ' + convert(varchar(12), @endRow)
exec(@totalSql)
END
GO